package design.donkey.auto.ddl.dialect.mysql;

import java.util.*;
import java.util.stream.Collectors;

import design.donkey.auto.ddl.dialect.AbstractDialect;
import design.donkey.auto.ddl.dialect.DdlOperation;
import design.donkey.auto.ddl.dialect.mysql.handler.ColumnHandler;
import design.donkey.auto.ddl.exception.AutoDdlException;
import design.donkey.auto.ddl.model.ColumnMetaModel;
import design.donkey.auto.ddl.model.IndexMetaModel;
import design.donkey.auto.ddl.model.TableMetaModel;
import design.donkey.auto.ddl.util.AutoDdlCommonUtils;
import design.donkey.framework.common.orm.annotation.JdbcType;
import design.donkey.framework.common.util.CommonUtils;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;

/**
 * MySql方言部分实现
 *
 * @author zhangjiakung
 * @since 0.0.1
 */
@Slf4j
public abstract class AbstractMySqlDialect extends AbstractDialect {

    /**
     * 关键字 {@value }
     */
    protected static final String COMMENT_KEYWORD = "comment";

    /**
     * 关键字 {@value }
     */
    protected static final String PRIMARY_KEYWORD = "PRIMARY";

    /**
     * 引擎 不设置时，使用数据库默认配置
     */
    protected String engine = "InnoDB";

    /**
     * 字符集
     */
    protected String charset;

    /**
     * 排序
     */
    protected String collate;

    /**
     * 是否 添加 IF NOT EXISTS
     */
    protected boolean createTableIfNotExists;

    /**
     * 列处理
     */
    protected LinkedList<ColumnHandler> columnHandlerList = new LinkedList<>();

    /**
     * 实例化数据库方言
     */
    protected AbstractMySqlDialect() {
        super("`");
    }

    @Override
    public List<String> create(TableMetaModel tableMetaModel, boolean format) {
        // 列信息列表
        List<String> columnSchemaList = createColumnSchema(tableMetaModel);
        // 索引信息
        List<String> indexSchemaList = createIndexSchema(tableMetaModel);
        // 元数据检查
        String name = tableMetaModel.getName();
        if (CommonUtils.isBlank(name)) {
            throw new AutoDdlException("表格名称为空");
        }
        // 表格选项
        List<String> tableOptionsList = new LinkedList<>();
        if (CommonUtils.isNotBlank(engine)) {
            tableOptionsList.add("ENGINE=" + engine);
        }
        if (CommonUtils.isAllNotBlank(charset, collate)) {
            tableOptionsList.add(String.format("DEFAULT CHARSET=%s COLLATE=%s", charset, collate));
        }

        if (CommonUtils.isNotBlank(tableMetaModel.getComment())) {
            tableOptionsList.add(String.format("COMMENT='%s'", tableMetaModel.getComment().replace("'", "\\'")));
        }
        // 生成建表语句
        String formatStr =
            createTableIfNotExists ? "CREATE TABLE IF NOT EXISTS %s (\n%s\n) %s" : "CREATE TABLE %s (\n%s\n) %s";
        if (!format) {
            formatStr = formatStr.replace("\n", "");
        }
        List<String> tableElementList = new ArrayList<>(columnSchemaList);
        tableElementList.addAll(indexSchemaList);
        String tableName = identifier(tableMetaModel.getName(), false);
        String columnIndexSchema = String.join(format ? ",\n" : ",", tableElementList);
        String tableOptions = String.join(" ", tableOptionsList);
        String createSchema = String.format(formatStr, tableName, columnIndexSchema, tableOptions);
        return Collections.singletonList(createSchema);
    }

    /**
     * 建表时列的 schema
     * 
     * @param tableMetaModel 表格元数据
     * @return 表格元数据
     */
    protected List<String> createColumnSchema(TableMetaModel tableMetaModel) {
        List<ColumnMetaModel> columns = tableMetaModel.getColumns();
        if (CommonUtils.isEmpty(columns)) {
            throw new AutoDdlException("表格不存在字段：" + tableMetaModel);
        }
        // 列信息转换
        List<String> resultList = new ArrayList<>();
        for (ColumnMetaModel column : columns) {
            List<String> columnSqlList = new ArrayList<>();
            // 写入列名称
            columnSqlList.add(identifier(column.getName(), false));
            if (CommonUtils.isNotBlank(column.getColumnDefinition())) {
                // 高度自定义列的ddl
                columnSqlList.add(column.getColumnDefinition());
            } else {
                // 程序自动处理
                for (ColumnHandler columnHandler : columnHandlerList) {
                    String handleValue = columnHandler.handle(column);
                    if (CommonUtils.isNotBlank(handleValue)) {
                        columnSqlList.add(handleValue);
                    }
                }
            }
            // 列信息拼接
            resultList.add(String.join(" ", columnSqlList));
        }
        return resultList;
    }

    /**
     * 建表时索引的 schema
     *
     * @param tableMetaModel 表格元数据
     * @return 表格元数据
     */
    protected List<String> createIndexSchema(TableMetaModel tableMetaModel) {
        List<IndexMetaModel> indexList = getIndexList(tableMetaModel);

        // 索引配置转换为 schema
        List<String> primaryKeyList = new ArrayList<>();
        List<String> uniqueList = new ArrayList<>();
        List<String> otherList = new ArrayList<>();
        for (IndexMetaModel indexMetaModel : indexList) {
            String columnJoin = Arrays.stream(indexMetaModel.getColumnNames()).map(s -> identifier(s, false))
                .collect(Collectors.joining(","));
            if (indexMetaModel.isPrimaryKey()) {
                // 主键处理方式
                primaryKeyList.add(String.format("PRIMARY KEY (%s)", columnJoin));
            } else {

                String indexName = indexMetaModel.getName();
                if (CommonUtils.isBlank(indexMetaModel.getName())) {
                    indexName = autoIndexName(indexMetaModel.isUnique(), tableMetaModel.getName(),
                        Arrays.asList(indexMetaModel.getColumnNames()));
                }
                if (indexMetaModel.isUnique()) {
                    // 唯一索引
                    uniqueList.add(String.format("UNIQUE KEY %s (%s)", indexName, columnJoin));
                } else {
                    otherList.add(String.format("KEY %s (%s)", indexName, columnJoin));
                }
            }

        }
        // 索引返回是存在先后顺序的
        List<String> resultList = new ArrayList<>(primaryKeyList);
        resultList.addAll(uniqueList);
        resultList.addAll(otherList);
        return resultList;
    }

    /**
     * 获取列信息去除一些重复的列
     * 
     * @param tableMetaModel 表格模型
     * @return 可用的索引模型
     */
    protected List<IndexMetaModel> getIndexList(TableMetaModel tableMetaModel) {
        // 转换列中的索引信息
        List<IndexMetaModel> columnIndexList = createColumnIndexConvert(tableMetaModel);
        List<IndexMetaModel> indexes = tableMetaModel.getIndexes();
        if (CommonUtils.isEmpty(indexes) && CommonUtils.isEmpty(columnIndexList)) {
            log.warn("没有任何索引" + tableMetaModel);
            return Collections.emptyList();
        }
        List<IndexMetaModel> indexList = new ArrayList<>(columnIndexList);
        indexList.addAll(indexes);

        // 索引配置检查是否合法
        Map<String, ColumnMetaModel> columnHub = new HashMap<>(tableMetaModel.getColumns().size());
        for (ColumnMetaModel column : tableMetaModel.getColumns()) {
            columnHub.put(identifier(column.getName(), true), column);
        }
        Map<String, IndexMetaModel> indexMetaModelHub = new LinkedHashMap<>();
        for (IndexMetaModel indexMetaModel : indexList) {
            // 索引没有列；
            if (CommonUtils.isEmpty(indexMetaModel.getColumnNames())) {
                throw new AutoDdlException("没有为索引指定列" + tableMetaModel);
            }
            LinkedHashSet<String> columnSet = new LinkedHashSet<>(Arrays.asList(indexMetaModel.getColumnNames()));
            if (CommonUtils.isEmpty(columnSet)) {
                throw new AutoDdlException("没有为索引指定列" + tableMetaModel);
            }
            if (columnSet.size() != indexMetaModel.getColumnNames().length) {
                throw new AutoDdlException("索引存在重复列" + tableMetaModel);
            }
            // 判断列是否真实存在
            for (String columnName : columnSet) {
                if (!columnHub.containsKey(identifier(columnName, true))) {
                    throw new AutoDdlException("索引中使用了不存的列 " + columnName + "  ->" + tableMetaModel);
                }
            }
            String indexKey = String.join(",", columnSet);
            indexMetaModelHub.put(indexKey, indexMetaModel);
        }

        return new ArrayList<>(indexMetaModelHub.values());
    }

    /**
     * 将列中有关的索引配置提取
     * 
     * @param tableMetaModel 表格细腻些
     * @return 索引数据
     */
    protected List<IndexMetaModel> createColumnIndexConvert(TableMetaModel tableMetaModel) {
        List<ColumnMetaModel> columns = tableMetaModel.getColumns();
        List<IndexMetaModel> uniqueColumnIndexList = new ArrayList<>();
        List<ColumnMetaModel> primaryKeyColumnList = new ArrayList<>();
        for (ColumnMetaModel column : columns) {
            if (column.isPrimaryKey()) {
                primaryKeyColumnList.add(column);
            } else if (column.isUnique()) {
                IndexMetaModel indexMetaModel = new IndexMetaModel();
                indexMetaModel.setUnique(true);
                indexMetaModel.setColumnNames(new String[] {column.getName()});
                uniqueColumnIndexList.add(indexMetaModel);
            }
        }

        List<IndexMetaModel> resultList = new ArrayList<>(uniqueColumnIndexList);
        if (CommonUtils.isNotEmpty(primaryKeyColumnList)) {
            IndexMetaModel primaryKey = new IndexMetaModel();
            primaryKey.setPrimaryKey(true);
            primaryKey
                .setColumnNames(primaryKeyColumnList.stream().map(ColumnMetaModel::getName).toArray(String[]::new));
            resultList.add(primaryKey);
        }
        return resultList;
    }

    @Override
    protected List<String> maintenance(String fullTableName, List<Statement> sourceStatements,
        List<Statement> targetStatements, Set<DdlOperation> operations) {
        // 参数检查
        if (CommonUtils.isEmpty(operations)) {
            log.warn("数据库 DDL 操作性不能为空！");
            return Collections.emptyList();
        }
        if (CommonUtils.isEmpty(sourceStatements) && CommonUtils.isEmpty(targetStatements)) {
            log.warn(" DDL 不能为空！");
            return Collections.emptyList();
        }
        // 删除历史表格
        if (CommonUtils.isNotEmpty(sourceStatements) && CommonUtils.isEmpty(targetStatements)
            && CommonUtils.contains(operations, DdlOperation.DROP_TABLE)) {
            return Collections.singletonList("删除表格");
        }
        // 创建表格
        if (CommonUtils.isNotEmpty(targetStatements) && CommonUtils.isEmpty(sourceStatements)
            && CommonUtils.contains(operations, DdlOperation.CREATE_TABLE)) {
            return targetStatements.stream().map(Objects::toString).toList();
        }
        // 表格变更
        if (CommonUtils.isNotEmpty(sourceStatements) && CommonUtils.isNotEmpty(targetStatements)) {
            List<String> maintenanceColumn =
                maintenanceColumn(fullTableName, sourceStatements, targetStatements, operations);
            List<String> maintenanceIndex =
                maintenanceIndex(fullTableName, sourceStatements, targetStatements, operations);
            List<String> maintenanceTableOptions =
                maintenanceTableOptions(fullTableName, sourceStatements, targetStatements, operations);

            List<String> maintenance = new ArrayList<>(maintenanceColumn);
            maintenance.addAll(maintenanceIndex);
            maintenance.addAll(maintenanceTableOptions);
            return maintenance;
        }
        return Collections.emptyList();
    }

    /**
     * 维护列信息
     *
     * @param fullTableName 表格名称
     * @param sourceStatements 源 Schema SQL 脚本
     * @param targetStatements 目标 Schema SQL 脚本
     * @param operations 生成的 维护 Schema SQL 脚本 允许出现的 ddl 类型
     * @return 需要执行的 SQL 脚本列表
     */
    protected List<String> maintenanceColumn(String fullTableName, List<Statement> sourceStatements,
        List<Statement> targetStatements, Set<DdlOperation> operations) {
        // 操作转换
        boolean isAdd = CommonUtils.contains(operations, DdlOperation.ADD_COLUMN);
        boolean isModify = CommonUtils.contains(operations, DdlOperation.MODIFY_COLUMN);
        boolean isDrop = CommonUtils.contains(operations, DdlOperation.DROP_COLUMN);
        // 获取列信息
        Map<String, ColumnDefinition> sourceColumnGroup = groupColumnByName(sourceStatements);
        Map<String, ColumnDefinition> targetColumnGroup = groupColumnByName(targetStatements);

        // 组织更新语句
        List<String> resultList = new ArrayList<>();
        // 删除列
        sourceColumnGroup.forEach((columnName, sourceColumnDefinition) -> {
            if (!targetColumnGroup.containsKey(columnName) && isDrop) {
                // 删除列
                resultList.add(alterTableDropColumn(fullTableName, sourceColumnDefinition.toString()));
            }
        });
        // 添加 修改
        targetColumnGroup.forEach((columnName, targetColumnDefinition) -> {
            if (sourceColumnGroup.containsKey(columnName)) {
                if (!equalsColumnDefinition(sourceColumnGroup.get(columnName), targetColumnDefinition) && isModify) {
                    // 修改列
                    resultList.add(alterTableModifyColumn(fullTableName, targetColumnDefinition.toString()));
                }
            } else {
                if (isAdd) {
                    // 添加列
                    resultList.add(alterTableAddColumn(fullTableName, targetColumnDefinition.toString()));
                }
            }
        });
        return resultList;
    }

    /**
     * 删除列
     *
     * @param fullTableName 表格名称
     * @param column 列
     * @return sql
     */
    protected String alterTableDropColumn(String fullTableName, String column) {
        return String.format("ALTER TABLE %s DROP %s", fullTableName, column);
    }

    /**
     * 添加列
     *
     * @param fullTableName 表格名称
     * @param column 列
     * @return sql
     */
    protected String alterTableAddColumn(String fullTableName, String column) {
        return String.format("ALTER TABLE %s ADD %s", fullTableName, column);
    }

    /**
     * 修改列
     *
     * @param fullTableName 表格名称
     * @param column 列
     * @return sql
     */
    protected String alterTableModifyColumn(String fullTableName, String column) {
        return String.format("ALTER TABLE %s MODIFY %s", fullTableName, column);
    }

    /**
     * 比较列是否是相同语义
     * 
     * @param the 列信息
     * @param that 列信息
     * @return 是否具有相同语义
     */
    protected boolean equalsColumnDefinition(ColumnDefinition the, ColumnDefinition that) {
        // 判断对象是否为同一个
        if (that == null || the == null) {
            return Objects.equals(the, that);
        }
        if (Objects.equals(the, that)) {
            return true;
        }
        // 比较数据类型
        ColDataType theDataType = the.getColDataType();
        ColDataType thatDataType = that.getColDataType();
        if (!CommonUtils.equalsIgnoreCase(theDataType.getDataType(), thatDataType.getDataType())) {
            return false;
        }
        // 取值范围比较
        if (theDataType.getDataType().toUpperCase().endsWith(JdbcType.INT.name())) {
            // 整数，存在0填充 则 M 值存在差异则表示不相对
            String zerofill = "zerofill";
            boolean noZerofill = (AutoDdlCommonUtils.containsIgnoreCase(theDataType.getArgumentsStringList(), zerofill)
                || AutoDdlCommonUtils.containsIgnoreCase(thatDataType.getArgumentsStringList(), zerofill))
                && AutoDdlCommonUtils.noEquals(theDataType.getArgumentsStringList(),
                    thatDataType.getArgumentsStringList());
            if (noZerofill) {
                return false;
            }
        } else if (AutoDdlCommonUtils.noEquals(theDataType.getArgumentsStringList(),
            thatDataType.getArgumentsStringList())) {
            return false;
        }
        // 比较其他约束是否相同
        if (!equalsColumnSpecIgnoreCase(the.getColumnSpecs(), that.getColumnSpecs(), true)) {
            return false;
        }
        return equalsColumnSpecIgnoreCase(that.getColumnSpecs(), the.getColumnSpecs(), false);
    }

    /**
     * 比较列属性
     *
     * @param theSpaceList theSpaceList
     * @param thatSpaceList thatSpaceList
     * @param ignoreCollate 是否忽略字符集
     * @return true 相同
     */
    protected boolean equalsColumnSpecIgnoreCase(List<String> theSpaceList, List<String> thatSpaceList,
        boolean ignoreCollate) {
        Map<String, String> specHub = new HashMap<>(Math.max(theSpaceList.size(), thatSpaceList.size()));
        for (String columnSpec : theSpaceList) {
            if (columnSpec.startsWith("'") || columnSpec.startsWith("\"")) {
                specHub.put(columnSpec, columnSpec);
            } else {
                specHub.put(columnSpec.toUpperCase(), columnSpec);
            }
        }
        for (String columnSpec : thatSpaceList) {
            String key =
                columnSpec.startsWith("'") || columnSpec.startsWith("\"") ? columnSpec : columnSpec.toUpperCase();
            if (!specHub.containsKey(key)) {
                if (ignoreCollate && "COLLATE".equalsIgnoreCase(key)) {
                    continue;
                }
                return false;
            }
        }
        return true;
    }

    /**
     * 列分组，根据列名称把列信息分组
     * 
     * @param statements sql 语句
     * @return 列分组信息
     */
    protected Map<String, ColumnDefinition> groupColumnByName(List<Statement> statements) {
        // 从建表语句中获取列信息
        CreateTable createTable =
            (CreateTable)statements.stream().filter(CreateTable.class::isInstance).findFirst().orElse(null);
        if (createTable == null) {
            return Collections.emptyMap();
        }
        List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
        if (CommonUtils.isEmpty(columnDefinitions)) {
            return Collections.emptyMap();
        }
        // 列信息解析
        Map<String, ColumnDefinition> result = new LinkedHashMap<>();
        for (ColumnDefinition columnDefinition : columnDefinitions) {
            String columnName = getColumnName(columnDefinition.getColumnName());
            result.put(columnName, columnDefinition);
        }
        return result;
    }

    /**
     * 格式化名称
     *
     * @param name 名称
     * @return 格式化后的名称
     */
    protected String getColumnName(String name) {
        return name == null ? null : identifier(name, true).toLowerCase();
    }

    /**
     * 维护索引信息
     *
     * @param fullTableName 表格名称
     * @param sourceStatements 源 Schema SQL 脚本
     * @param targetStatements 目标 Schema SQL 脚本
     * @param operations 生成的 维护 Schema SQL 脚本 允许出现的 ddl 类型
     * @return 需要执行的 SQL 脚本列表
     */
    protected List<String> maintenanceIndex(String fullTableName, List<Statement> sourceStatements,
        List<Statement> targetStatements, Set<DdlOperation> operations) {
        // 操作转换
        boolean isAdd = CommonUtils.contains(operations, DdlOperation.ADD_INDEX);
        boolean isRename = CommonUtils.contains(operations, DdlOperation.RENAME_INDEX);
        boolean isDrop = CommonUtils.contains(operations, DdlOperation.DROP_INDEX);
        // 获取索引信息
        Map<String, Index> sourceIndexGroup = groupIndexByColumn(sourceStatements);
        Map<String, Index> targetIndexGroup = groupIndexByColumn(targetStatements);
        // 组织更新语句
        List<String> resultList = new ArrayList<>();
        // 删除列
        sourceIndexGroup.forEach((indexKey, sourceIndex) -> {
            if (!targetIndexGroup.containsKey(indexKey) && isDrop) {
                // 删除索引
                resultList.add(alterTableDropIndex(fullTableName, sourceIndex.toString()));
            }
        });
        // 添加 修改
        targetIndexGroup.forEach((indexKey, targetIndex) -> {
            if (sourceIndexGroup.containsKey(indexKey)) {
                String sourceIndexName = getIndexName(sourceIndexGroup.get(indexKey));
                String targetIndexName = getIndexName(targetIndex);
                if (!CommonUtils.equalsIgnoreCase(sourceIndexName, targetIndexName) && isRename) {
                    // 修改索引名称
                    resultList.add(alterTableRenameIndex(fullTableName, sourceIndexName, targetIndexName));
                }
            } else {
                if (isAdd) {
                    // 添加索引
                    resultList.add(alterTableAddIndex(fullTableName, targetIndex.toString()));
                }
            }
        });
        return resultList;
    }

    /**
     * 删除索引
     *
     * @param fullTableName 表格名称
     * @param index 索引
     * @return sql
     */
    protected String alterTableDropIndex(String fullTableName, String index) {
        return String.format("ALTER TABLE %s DROP %s", fullTableName, index);
    }

    /**
     * 添加索引
     *
     * @param fullTableName 表格名称
     * @param index 索引
     * @return sql
     */
    protected String alterTableAddIndex(String fullTableName, String index) {
        return String.format("ALTER TABLE %s ADD %s", fullTableName, index);
    }

    /**
     * 添加索引
     *
     * @param fullTableName 表格名称
     * @param sourceIndexName 变更前的索引名称
     * @param targetIndexName 变更后的索引名称
     * @return sql
     */
    protected String alterTableRenameIndex(String fullTableName, String sourceIndexName, String targetIndexName) {
        return String.format("ALTER TABLE %s RENAME INDEX %s TO %s", fullTableName, sourceIndexName, targetIndexName);
    }

    /**
     * 索引分组，索引所使用的列及类型分组
     *
     * @param statements sql 语句
     * @return 列分组信息
     */
    protected Map<String, Index> groupIndexByColumn(List<Statement> statements) {
        // 从建表语句中获取列信息
        CreateTable createTable =
            (CreateTable)statements.stream().filter(CreateTable.class::isInstance).findFirst().orElse(null);
        if (createTable == null) {
            return Collections.emptyMap();
        }
        List<Index> indexes = createTable.getIndexes();
        if (CommonUtils.isEmpty(indexes)) {
            return Collections.emptyMap();
        }
        // 检查索引是否合规
        checkRepeatIndexName(indexes);
        // 解析
        Map<String, Index> indexHub = new LinkedHashMap<>();
        for (Index index : indexes) {
            List<String> keyList = new ArrayList<>(index.getColumns().size() + 1);
            // 添加索引类型
            keyList.add(index.getType().toUpperCase());
            // 添加列名称
            for (Index.ColumnParams column : index.getColumns()) {
                keyList.add(getColumnName(column.getColumnName()));
            }
            // 使用逗号分割连接作为key
            String key = String.join(",", keyList);
            indexHub.put(key, index);
        }
        return indexHub;
    }

    /**
     * 检查重复的索引名称
     *
     * @param indexList 索引列表
     */
    protected void checkRepeatIndexName(List<Index> indexList) {
        if (CommonUtils.isEmpty(indexList)) {
            return;
        }
        Map<String, String> hub = new HashMap<>(indexList.size());
        for (Index index : indexList) {
            String indexName = getIndexName(index);
            if (hub.containsKey(indexName)) {
                throw new AutoDdlException("出现了重复的索引名称" + indexName);
            }
            hub.put(indexName, indexName);
        }
    }

    /**
     * 获取索引名称
     *
     * @param index 索引
     *
     * @return 索引名称
     */
    protected String getIndexName(Index index) {
        if (index == null) {
            return null;
        }
        if (index.getType().toUpperCase().startsWith(PRIMARY_KEYWORD)) {
            return "";
        }
        if (CommonUtils.isNotBlank(index.getName())) {
            return identifier(index.getName(), true).toLowerCase();
        }
        return identifier(index.getColumnsNames().get(0), true).toLowerCase();
    }

    /**
     * 维护表格选项信息
     *
     * @param fullTableName 表格名称
     * @param sourceStatements 源 Schema SQL 脚本
     * @param targetStatements 目标 Schema SQL 脚本
     * @param operations 生成的 维护 Schema SQL 脚本 允许出现的 ddl 类型
     * @return 需要执行的 SQL 脚本列表
     */
    protected List<String> maintenanceTableOptions(String fullTableName, List<Statement> sourceStatements,
        List<Statement> targetStatements, Set<DdlOperation> operations) {
        // 不允许修改表格
        if (!CommonUtils.contains(operations, DdlOperation.MODIFY_TABLE)) {
            return Collections.emptyList();
        }
        // 获取表格信息
        List<String> sourceTableOptions = getTableOptions(sourceStatements);
        List<String> targetTableOptions = getTableOptions(targetStatements);
        // ignore
        if (AutoDdlCommonUtils.containsIgnoreCase(sourceTableOptions, COMMENT_KEYWORD)
            || AutoDdlCommonUtils.containsIgnoreCase(targetTableOptions, COMMENT_KEYWORD)) {
            // 可能需要更新 comment
            String sourceTableComment = getTableComment(sourceTableOptions);
            String targetTableComment = getTableComment(targetTableOptions);
            if (Objects.equals(targetTableComment, sourceTableComment)) {
                return Collections.emptyList();
            }
            // 修改表格备注
            return Collections.singletonList(alterTableComment(fullTableName, targetTableComment));
        }
        return Collections.emptyList();
    }

    /**
     * 修改表格语句
     * 
     * @param fullTableName 表格名称
     * @param comment 注释
     * @return 修改表格语句
     */
    protected String alterTableComment(String fullTableName, String comment) {
        return String.format("ALTER TABLE %s COMMENT %s", fullTableName, CommonUtils.isBlank(comment) ? "''" : comment);
    }

    /**
     * 获取表格属性
     * 
     * @param statements Schema SQL 脚本
     * @return 需要执行的 SQL 脚本列表
     */
    protected List<String> getTableOptions(List<Statement> statements) {
        // 从建表语句中获取列信息
        CreateTable createTable =
            (CreateTable)statements.stream().filter(CreateTable.class::isInstance).findFirst().orElse(null);
        if (createTable == null) {
            return Collections.emptyList();
        }
        List<String> options = createTable.getTableOptionsStrings();
        if (CommonUtils.isEmpty(options)) {
            return Collections.emptyList();
        }
        return options;
    }

    /**
     * 获取表格备注
     *
     * @param tableOptionList 表格配置
     * @return null 表格不存在备注
     */
    @SuppressWarnings("all")
    protected String getTableComment(List<String> tableOptionList) {
        if (CommonUtils.isEmpty(tableOptionList)) {
            return null;
        }
        for (int i = 0; i < tableOptionList.size(); i++) {
            String tableOption = tableOptionList.get(i);
            if (COMMENT_KEYWORD.equalsIgnoreCase(tableOption)) {
                if (i + 1 >= tableOptionList.size()) {
                    return null;
                }
                int commentIndex = "=".equalsIgnoreCase(tableOptionList.get(i + 1)) ? i + 2 : i + 1;
                if (commentIndex < tableOptionList.size()) {
                    String comment = tableOptionList.get(commentIndex);
                    if (comment.startsWith("'") || comment.startsWith("\"")) {
                        return comment;
                    }
                }
            }
        }
        return null;
    }

    @Override
    public String getProductName() {
        return "MySql";
    }

    /**
     * 自动生成索引名称
     * 
     * @param unique 是否唯一
     * @param fullTableName 表格名称
     * @param columnList 列信息
     * @return 索引名称
     */
    @SuppressWarnings("all")
    protected String autoIndexName(boolean unique, String fullTableName, List<String> columnList) {
        // 唯一索引直接返回列名称作为索引名称
        if (unique && columnList.size() == 1) {
            return identifier(columnList.get(0), false);
        }
        // 索引名称做大长度
        int indexNameMaxLength = 28;
        // 索引名称中使用到的列、表名称最少保留几个字符；一次缩短几个字符
        int indexNameElementMinLength = 3;
        // 是否可以缩短表名称
        boolean isRemoveTableNext;
        // 是否可以缩短列名称
        boolean isRemoveColumnNext;
        // 当前缩短列、表名称的次数
        int removeColumnNextCount = 1;
        int removeTableNextCount = 1;
        do {
            isRemoveTableNext = false;
            isRemoveColumnNext = false;
            List<String> indexNameList = new ArrayList<>(columnList.size() + 2);
            indexNameList.add(unique ? "uiq" : "idx");

            // 精简列名称
            for (String column : columnList) {
                column = getColumnName(column);
                String columnName = removeIdentifier(column);
                columnName = streamlineName(columnName, indexNameElementMinLength, indexNameElementMinLength,
                    removeColumnNextCount);
                indexNameList.add(columnName);
                if (columnName.length() > indexNameElementMinLength) {
                    isRemoveColumnNext = true;
                }
            }
            removeColumnNextCount++;

            // 精简表名称
            String tableName = removeIdentifier(fullTableName.replace(".", "_"));
            if (!isRemoveColumnNext) {
                tableName = streamlineName(tableName, indexNameElementMinLength, indexNameElementMinLength,
                    removeTableNextCount);
                removeTableNextCount++;
            }
            indexNameList.add(1, tableName);
            if (tableName.length() > indexNameElementMinLength) {
                isRemoveTableNext = true;
            }

            // 判断精简后的名称是否符号长度要求
            String indexName = String.join(AutoDdlCommonUtils.UNDERLINE, indexNameList);
            if (indexName.length() <= indexNameMaxLength) {
                return identifier(indexName, false);
            }
            // 表名称或列名称其中一个或多个大于3个字符就仍然可以缩减
        } while (isRemoveTableNext || isRemoveColumnNext);

        // 不能自动生成
        throw new AutoDdlException(fullTableName + " 索引名称使用算法生成异常!");
    }

    /**
     * 删除关键字包裹对象
     * 
     * @param value value
     * @return value
     */
    public String removeIdentifier(String value) {
        String identifier = identifier(value, true);
        if (CommonUtils.isBlank(value)) {
            return value;
        }
        return identifier.substring(1, identifier.length() - 1);
    }

    /**
     * 精简名称
     *
     * @param name 名称
     * @param minLength 最小长度
     * @param maxStepLength 每次最大精简步长，即最多一次删除几个字符
     * @param maxCount 最大精简次数
     * @return 精简后的名称
     */
    protected String streamlineName(String name, int minLength, int maxStepLength, int maxCount) {
        if (name.length() <= minLength) {
            return name;
        }
        String[] nameArray = name.split(AutoDdlCommonUtils.UNDERLINE);
        int count = 1;
        for (; count <= maxCount; count++) {
            boolean isNext = false;
            for (int i = 0; i < nameArray.length; i++) {
                String nameArrayElement = nameArray[i];
                if (nameArrayElement.length() <= minLength) {
                    continue;
                }
                int subEndIndex = nameArrayElement.length() - (maxStepLength * count);
                nameArray[i] = nameArrayElement.substring(0, Math.max(subEndIndex, minLength));
                // 判断是否可以继续缩减
                if (nameArray[i].length() > minLength) {
                    isNext = true;
                }
            }
            // 不可以缩减则直接退出循环
            if (!isNext) {
                break;
            }
        }
        return joinName(count, maxCount, nameArray);
    }

    /**
     * 拼接精简名称
     *
     * @param count 精简次数
     * @param maxCount 最大精简次数
     * @param nameArray 精简后的名称列表
     * @return 拼接后的名称
     */
    protected String joinName(int count, int maxCount, String[] nameArray) {
        if (count > maxCount) {
            return String.join(AutoDdlCommonUtils.UNDERLINE, nameArray);
        }

        for (int to = nameArray.length; to > 1; to--) {
            String[] newNameArray = Arrays.copyOfRange(nameArray, 0, to);
            count++;
            if (count > maxCount) {
                return String.join(AutoDdlCommonUtils.UNDERLINE, newNameArray);
            }
        }
        return nameArray[0];
    }
}
